﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using UniMana.Utils.Controls;
using UniMana.Data;

namespace UniMana.Data
{
    public static class Training_ClassData
    {
        #region [An]
        public static IQueryable GetSubjectClass(this Table<Training_Class> nClass,Table<Training_Student> nStudent, 
            Table<Training_Student_Class> nStudentClass, Table<Training_Student_Subject_Class> nStudentSubjectClass, 
            Table<Training_Subject_Class> nSubjectClass, int _iClassKey,int _iTermKey)
        {
            var iQuery = from t in nStudentClass
                         join t0 in nStudentSubjectClass on t.Training_StudentKey equals t0.Training_StudentKey
                         join t1 in nSubjectClass on t0.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                         join t2 in nStudent on t.Training_StudentKey equals t2.Training_StudentKey
                         where
                           t.Training_ClassKey == _iClassKey &&
                           t1.Catalogue_TermKey == _iTermKey
                         orderby
                            t1.Training_Subject_ClassKey,
                            t2.SureName
                         select new
                         {
                             t.ID,
                             t2.LastName,
                             t2.SureName,
                             Gender = t2.Gender == true ? "Nam" : "Nữ",
                             t2.Birthday,
                             t.Training_StudentKey,
                             Training_Subject_ClassKey = (System.Int32?)t1.Training_Subject_ClassKey
                         };
            return iQuery;
        }

        public static IQueryable GetSubjectClass_Fail(this Table<Training_Class> nClass, Table<Training_Student> nStudent, Table<Training_Student_Class> nStudentClass, Table<Training_Student_Subject_Class> nStudentSubjectClass, Table<Training_Subject_Class> nSubjectClass, Table<Training_Score> nScore, Table<Training_SubjectClass_ScoreType> nSubjectClassScoreType, Table<Training_Subject> nSubject, int _iClassKey,bool _bYearly = false)
        {
            if (_bYearly)
            {
                
                var iQuery = from t in nStudentClass
                             join t0 in nStudentSubjectClass on t.Training_StudentKey equals t0.Training_StudentKey
                             join t1 in nSubjectClass on t0.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                             join t2 in nStudent on t.Training_StudentKey equals t2.Training_StudentKey
                             join t5 in nSubject on t1.Training_SubjectKey equals t5.Training_SubjectKey
                             join t3 in nScore
                                   on new { t.Training_StudentKey, t1.Training_Subject_ClassKey }
                               equals new { t3.Training_StudentKey, t3.Training_Subject_ClassKey }
                             join t4 in nSubjectClassScoreType
                                   on new { t0.Training_Subject_ClassKey, Column1 = t1.Training_Subject_ClassKey, t3.Training_ScoreTypeKey }
                               equals new { t4.Training_Subject_ClassKey, Column1 = t4.Training_Subject_ClassKey, t4.Training_ScoreTypeKey }
                             orderby
                           t1.Catalogue_SchoolYearKey,
                           t1.Catalogue_TermKey,
                           t1.Training_SubjectKey,
                           t2.Training_StudentKey,
                           t4.Training_ScoreTypeKey
                             where
                               t.Training_ClassKey == _iClassKey
                               && t.Catalogue_StatusKey == 1
                             select new
                             {
                                 t2.Training_StudentKey,
                                 t.ID,
                                 t2.LastName,
                                 t2.SureName,
                                 Gender = t2.Gender == UniMana.Utils.Configs.DefaultConfig.FEMALE ? "Nữ" : "Nam",
                                 t2.Birthday,
                                 t2.HomePhone,
                                 t2.CellPhone,
                                 t2.Email,
                                 Training_Subject_ClassKey = (System.Int32?)t1.Training_Subject_ClassKey,
                                 ClassID = t1.ID,
                                 t1.Name,
                                 t1.Training_SubjectKey,
                                 t1.Catalogue_SchoolYearKey,
                                 t1.Catalogue_TermKey,
                                 t3.Score,
                                 Training_ScoreTypeKey = (System.Int32?)t4.Training_ScoreTypeKey,
                                 t4.DefaultPercent,
                                 t.Training_ClassKey,
                                 SubjectName = t5.Name
                                 //CaScore = t3.Score * t4.DefaultPercent
                             };
                return iQuery;
            }
            else
            {
                var iQueryStudent = from student in nStudentClass
                                    where student.Catalogue_StatusKey == 1
                                    select new
                                    {
                                        student.Training_StudentKey
                                    };
                var iQuery = from t in nStudentClass
                             join t0 in nStudentSubjectClass on t.Training_StudentKey equals t0.Training_StudentKey
                             join t1 in nSubjectClass on t0.Training_Subject_ClassKey equals t1.Training_Subject_ClassKey
                             join t2 in nStudent on t.Training_StudentKey equals t2.Training_StudentKey
                             join t3 in nSubject on new { Training_SubjectKey = Convert.ToInt32(t1.Training_SubjectKey) } equals new { Training_SubjectKey = t3.Training_SubjectKey }
                             join t4 in nScore
                                   on new { t.Training_StudentKey, t1.Training_Subject_ClassKey }
                               equals new { t4.Training_StudentKey, t4.Training_Subject_ClassKey }
                             join t5 in nSubjectClassScoreType
                                   on new { t0.Training_Subject_ClassKey, Column1 = t1.Training_Subject_ClassKey, t4.Training_ScoreTypeKey }
                               equals new { t5.Training_Subject_ClassKey, Column1 = t5.Training_Subject_ClassKey, t5.Training_ScoreTypeKey }
                             group new { t2, t1, t, t3, t4, t5 } by new
                             {
                                 t2.Training_StudentKey,
                                 t1.Training_SubjectKey,
                                 t1.Catalogue_SchoolYearKey,
                                 t1.Catalogue_TermKey,
                                 t.ID,
                                 t2.LastName,
                                 t2.SureName,
                                 t2.Gender,
                                 t2.Birthday,
                                 t2.HomePhone,
                                 t2.CellPhone,
                                 t2.Email,
                                 t3.Name,
                                 t.Training_ClassKey
                             } into g
                             where g.Key.Training_ClassKey == _iClassKey
                             orderby
                               g.Key.Catalogue_SchoolYearKey,
                               g.Key.Catalogue_TermKey,
                               g.Key.Training_SubjectKey,
                               g.Key.Training_StudentKey
                             select new
                             {
                                 g.Key.Training_StudentKey,
                                 g.Key.ID,
                                 g.Key.LastName,
                                 g.Key.SureName,
                                 Gender = (System.Boolean?)g.Key.Gender,
                                 Birthday = (System.DateTime?)g.Key.Birthday,
                                 g.Key.HomePhone,
                                 g.Key.CellPhone,
                                 g.Key.Email,
                                 Training_SubjectKey = (System.Int32?)g.Key.Training_SubjectKey,
                                 Catalogue_SchoolYearKey = (System.Int32?)g.Key.Catalogue_SchoolYearKey,
                                 Catalogue_TermKey = (System.Int32?)g.Key.Catalogue_TermKey,
                                 Score = (System.Double?)g.Sum(p => p.t4.Score * p.t5.DefaultPercent) / (System.Double?)g.Sum(p => p.t5.DefaultPercent), //Tổng điểm
                                 SubjectName = g.Key.Name
                             };
                iQuery = from iquery in iQuery
                         from iquerystudent in iQueryStudent
                         where iquery.Training_StudentKey == iquerystudent.Training_StudentKey
                         select iquery;
                return iQuery;
            }
            
        }

        public static IQueryable GetSubjectClassFail(this Table<Training_Class> nClass, Table<Training_Student_Class> nTraining_Student_Class,Table<Training_Student> nTraining_Student,Table<Training_Subject_Class> nTraining_Subject_Class, Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class,Table<Training_Score_Transfer> nTraining_Score_Transfer,Table<Training_Score> nTraining_Score, int _iClassKey)
        {
            var Training_Score = (from training_Score in nTraining_Score
                                 select training_Score.Training_Subject_ClassKey).Distinct();

            var ListStudent = from Class in nTraining_Student_Class
                              from Student in nTraining_Student
                              where Class.Training_StudentKey == Student.Training_StudentKey && Class.Training_ClassKey == _iClassKey && Class.Mainly == true && Class.Catalogue_StatusKey == 1
                              select new
                              {
                                  Student.Training_StudentKey,
                                  Class.ID,
                                  Student.LastName,
                                  Student.SureName,
                                  Gender = Student.Gender == UniMana.Utils.Configs.DefaultConfig.FEMALE ? "Nữ" : "Nam",
                                  Birthday = Student.ExceptionBirthday != null ? Student.ExceptionBirthday.ToString() :  Student.Birthday.Value.Day  + "/" + Student.Birthday.Value.Month + "/" + Student.Birthday.Value.Year,
                                  Student.CellPhone,
                                  Student.Email,
                                  Class.Training_TrainingProgramKey
                              };
            var ListScore = from listStudent in ListStudent
                            from studentSubjectClass in nTraining_Student_Subject_Class
                            where
                                listStudent.Training_StudentKey == studentSubjectClass.Training_StudentKey
                                && listStudent.Training_TrainingProgramKey == studentSubjectClass.Training_TrainingProgramKey
                            select new
                            {
                                listStudent.Training_StudentKey,
                                listStudent.ID,
                                listStudent.LastName,
                                listStudent.SureName,
                                listStudent.Gender,
                                listStudent.Birthday,
                                listStudent.CellPhone,
                                listStudent.Email,
                                listStudent.Training_TrainingProgramKey,
                                studentSubjectClass.Training_Subject_ClassKey,
                                studentSubjectClass.AverageScoreFinal
                            };

            var ListStudentAndScore = from listScore in ListScore
                                      from subjectClass in nTraining_Subject_Class
                                      where listScore.Training_Subject_ClassKey == subjectClass.Training_Subject_ClassKey && Training_Score.Contains(subjectClass.Training_Subject_ClassKey)
                                      select new
                                      {
                                          listScore.Training_StudentKey,
                                          listScore.ID,
                                          listScore.LastName,
                                          listScore.SureName,
                                          listScore.Gender,
                                          listScore.Birthday,
                                          listScore.CellPhone,
                                          listScore.Email,
                                          listScore.Training_TrainingProgramKey,
                                          listScore.Training_Subject_ClassKey,
                                          subjectClass.Training_SubjectKey,
                                          listScore.AverageScoreFinal
                                      };

            var ScoreTransfer = from listStudent in ListStudent
                                from scoreTransfer in nTraining_Score_Transfer
                                where
                                listStudent.Training_StudentKey == scoreTransfer.Training_StudentKey
                                && listStudent.Training_TrainingProgramKey == scoreTransfer.Training_TrainingProgramKeyNew
                                select new
                                {
                                    listStudent.Training_StudentKey,
                                    listStudent.ID,
                                    listStudent.LastName,
                                    listStudent.SureName,
                                    listStudent.Gender,
                                    listStudent.Birthday,
                                    listStudent.CellPhone,
                                    listStudent.Email,
                                    listStudent.Training_TrainingProgramKey,
                                    scoreTransfer.Training_Subject_ClassKey,
                                    Training_TrainingProgramKeyOld = scoreTransfer.Training_TrainingProgramKey
                                };

            var ListStudentAndScore1 = from scoreTransfer in ScoreTransfer
                                       from studentSubjectClass in nTraining_Student_Subject_Class
                                       from subjectClass in nTraining_Subject_Class
                                       where scoreTransfer.Training_StudentKey == studentSubjectClass.Training_StudentKey
                                       && scoreTransfer.Training_Subject_ClassKey == studentSubjectClass.Training_Subject_ClassKey
                                       && scoreTransfer.Training_Subject_ClassKey == subjectClass.Training_Subject_ClassKey
                                       && scoreTransfer.Training_TrainingProgramKeyOld == studentSubjectClass.Training_TrainingProgramKey
                                       && Training_Score.Contains(subjectClass.Training_Subject_ClassKey)
                                       select new
                                       {
                                           scoreTransfer.Training_StudentKey,
                                           scoreTransfer.ID,
                                           scoreTransfer.LastName,
                                           scoreTransfer.SureName,
                                           scoreTransfer.Gender,
                                           scoreTransfer.Birthday,
                                           scoreTransfer.CellPhone,
                                           scoreTransfer.Email,
                                           scoreTransfer.Training_TrainingProgramKey,
                                           subjectClass.Training_Subject_ClassKey,
                                           subjectClass.Training_SubjectKey,
                                           studentSubjectClass.AverageScoreFinal
                                       };
            //ListStudentAndScore = from listStudentAndScore in ListStudentAndScore
                                  
            
            ListStudentAndScore = ListStudentAndScore.Union(ListStudentAndScore1).OrderByDescending(p => p.Training_Subject_ClassKey) ;
            return ListStudentAndScore;            
        }

        public static IQueryable GetScoreTransfer(this Table<Training_Class> nClass, Table<Training_Student_Class> nTraining_Student_Class, Table<Training_Student> nTraining_Student, Table<Training_Subject_Class> nTraining_Subject_Class, Table<Training_Student_Subject_Class> nTraining_Student_Subject_Class, Table<Training_Score_Transfer> nTraining_Score_Transfer, int _iClassKey)
        {
            var ListStudent = from Class in nTraining_Student_Class
                              from Student in nTraining_Student
                              where Class.Training_StudentKey == Student.Training_StudentKey && Class.Training_ClassKey == _iClassKey && Class.Mainly == true
                              select new
                              {
                                  Student.Training_StudentKey,
                                  Class.ID,
                                  Student.LastName,
                                  Student.SureName,
                                  Student.Birthday,
                                  Class.Training_TrainingProgramKey
                              };
            var ScoreTransfer = from listStudent in ListStudent
                                from scoreTransfer in nTraining_Score_Transfer
                                where 
                                listStudent.Training_StudentKey == scoreTransfer.Training_StudentKey
                                && listStudent.Training_TrainingProgramKey == scoreTransfer.Training_TrainingProgramKeyNew
                                select new
                                {
                                    listStudent.Training_StudentKey,
                                    listStudent.ID,
                                    listStudent.LastName,
                                    listStudent.SureName,
                                    scoreTransfer.Training_Subject_ClassKey,
                                    scoreTransfer.Training_TrainingProgramKey
                                };
            var ListStudentAndScore1 = from scoreTransfer in ScoreTransfer
                                       from studentSubjectClass in nTraining_Student_Subject_Class
                                       from subjectClass in nTraining_Subject_Class
                                       where scoreTransfer.Training_StudentKey == studentSubjectClass.Training_StudentKey
                                       && scoreTransfer.Training_Subject_ClassKey == studentSubjectClass.Training_Subject_ClassKey
                                       && scoreTransfer.Training_Subject_ClassKey == subjectClass.Training_Subject_ClassKey
                                       && scoreTransfer.Training_TrainingProgramKey == studentSubjectClass.Training_TrainingProgramKey
                                       select new
                                       {
                                           scoreTransfer.Training_StudentKey,
                                           scoreTransfer.ID,
                                           scoreTransfer.LastName,
                                           scoreTransfer.SureName,
                                           subjectClass.Training_Subject_ClassKey,
                                           subjectClass.Training_SubjectKey,
                                           studentSubjectClass.AverageScoreFinal
                                       };
            return ListStudentAndScore1;
        }

        /// <summary>
        /// Lấy danh sách lớp học phần có ID gần giống với ID truyền vào
        /// </summary>
        /// <param name="t1"></param>
        /// <param name="ClassID">ID lớp học phần</param>
        /// <returns></returns>
        public static IQueryable GetLikeClassList(this Table<Training_Class> t1, string ClassID)
        {
            var sl = from s in t1
                     where s.ID.Contains(ClassID)
                     select new
                     {
                         s.Training_ClassKey,
                         s.ID,
                         s.Name
                     };
            return sl;
        }


        /// <summary>
        /// Lấy thông tin lớp với Khoa, bậc, ngành, loại hình đào tạo, khóa học
        /// </summary>
        /// <param name="m_iDepartment"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <param name="m_iCourse"></param>

        public static IQueryable GetListClassWithDepartmentLevelMajorTrainingTypeCourse(this Table<Training_Class> nTraining_Class,
            int m_iDepartment,
            int m_iLevel,
            int m_iMajor,
            int m_iTrainingType,
            int m_iCourse)
        {
            var values = from t in nTraining_Class
                         where t.Catalogue_DepartmentKey == m_iDepartment &&
                         t.Catalogue_LevelKey == m_iLevel &&
                         t.Catalogue_MajorKey == m_iMajor &&
                         t.Catalogue_TrainingType == m_iTrainingType &&
                         t.Catalogue_CourseKey == m_iCourse
                         select t;
            return values;
        }

        /// <summary>
        /// Lấy mã lớp tên lớp
        /// </summary>
        /// <param name="n_tc"></param>
        /// <returns></returns>

        public static IQueryable GetKeyNameClass(this Table<Training_Class> nTraining_Class)
        {
            var values = from t in nTraining_Class
                         select new
                         {
                             t.Training_ClassKey,
                             t.Name
                         };
            return values;
        }

        /// <summary>
        /// Lấy danh sách lớp với mã ID hoặc các thông tin Khoa, ngành, bậc, loại  hình, khóa học
        /// </summary>
        /// <param name="nTraining_Class"></param>
        /// <param name="m_strID"></param>
        /// <param name="m_iDepartment"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iType"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static IQueryable GetListClassWithID(this Table<Training_Class> nTraining_Class,
            string m_strID,
            int m_iDepartment,
            int m_iMajor,
            int m_iLevel,
            int m_iType,
            int m_iCourse)
        {
            var values = from t in nTraining_Class
                         select new
                         {
                             t.Training_ClassKey,
                             t.Personnel_PersonnelKey,
                             t.ID,
                             t.Name,
                             t.Catalogue_DepartmentKey,
                             t.Catalogue_MajorKey,
                             t.Catalogue_LevelKey,
                             t.Catalogue_TrainingType,
                             t.Catalogue_CourseKey
                         };
            if (m_strID != "")
            {
                values = values.Where(tb => tb.ID.Contains(m_strID));
                //if (m_iDepartment > 0)
                //    values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartment);
            }
            else
            {
                if (m_iDepartment > 0)
                    values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartment);
                if (m_iMajor > 0)
                    values = values.Where(tb => tb.Catalogue_MajorKey == m_iMajor);
                if (m_iLevel > 0)
                    values = values.Where(tb => tb.Catalogue_LevelKey == m_iLevel);
                if (m_iType > 0)
                    values = values.Where(tb => tb.Catalogue_TrainingType == m_iType);
                if (m_iCourse > 0)
                    values = values.Where(tb => tb.Catalogue_CourseKey == m_iCourse);
            }
            return values;
        }

        /// <summary>
        /// Kiểm tra mã lớp trùng nhau
        /// </summary>
        /// <param name="nTraining_Class"></param>
        /// <param name="m_strID"></param>
        /// <returns></returns>

        public static bool CheckIDClassIdentical(this Table<Training_Class> nTraining_Class,
            string m_strID)
        {
            var values = from t in nTraining_Class
                         where t.ID == m_strID
                         select t;
            if (values.Count() > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// Lấy ra danh sách lớp với các điều kiện truyền vào
        /// </summary>
        /// <param name="trnClass"></param>
        /// <param name="perPerson"></param>
        /// <param name="m_iDepartment"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <param name="m_iCourse"></param>
        /// <param name="m_iLevel"></param>
        /// <returns></returns>

        public static IQueryable GetClassByDepartmentMajorTraningTypeCourseLevel(this Table<Training_Class> nTraining_Class,
            Table<Personnel_Personnel> nPersonnel_Personnel,
            int m_iDepartment,
            int m_iMajor,
            int m_iTrainingType,
            int m_iCourse,
            int m_iLevel)
        {
            var classList = from cls in nTraining_Class
                            join person in nPersonnel_Personnel on cls.Personnel_PersonnelKey equals person.Personnel_PersonnelKey
                            select new
                            {
                                cls.Training_ClassKey,
                                cls.Personnel_PersonnelKey,
                                cls.ID,
                                cls.Name,
                                person.LastName,
                                firstname = person.Name,
                                cls.Catalogue_LevelKey,
                                cls.Catalogue_CourseKey,
                                cls.Catalogue_TrainingType,
                                cls.Catalogue_MajorKey,
                                cls.Catalogue_DepartmentKey
                            };
            if (m_iDepartment > 0)
            {
                classList = classList.Where(p => p.Catalogue_DepartmentKey == int.Parse(m_iDepartment.ToString()));
            }
            if (m_iMajor > 0)
            {
                classList = classList.Where(p => p.Catalogue_MajorKey == int.Parse(m_iMajor.ToString()));
            }
            if (m_iTrainingType > 0)
            {
                classList = classList.Where(p => p.Catalogue_TrainingType == int.Parse(m_iTrainingType.ToString()));
            }
            if (m_iCourse > 0)
            {
                classList = classList.Where(p => p.Catalogue_CourseKey == int.Parse(m_iCourse.ToString()));
            }
            if (m_iLevel > 0)
            {
                classList = classList.Where(p => p.Catalogue_LevelKey == int.Parse(m_iLevel.ToString()));
            }
            return classList.Distinct();
        }


        /// <summary>
        /// Lấy danh sách lớp với tên lớp hoặc các thông tin Khoa, ngành, bậc, loại  hình, khóa học
        /// </summary>
        /// <param name="nTraining_Class"></param>
        /// <param name="m_strID"></param>
        /// <param name="m_iDepartment"></param>
        /// <param name="m_iMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <param name="m_iType"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static IQueryable GetListClassWithName(this Table<Training_Class> nTraining_Class,
            string m_strName,
            int m_iDepartment,
            int m_iMajor,
            int m_iLevel,
            int m_iType,
            int m_iCourse)
        {
            var values = from t in nTraining_Class
                         select new
                         {
                             t.Training_ClassKey,
                             t.Personnel_PersonnelKey,
                             t.ID,
                             t.Name,
                             t.Catalogue_DepartmentKey,
                             t.Catalogue_MajorKey,
                             t.Catalogue_LevelKey,
                             t.Catalogue_TrainingType,
                             t.Catalogue_CourseKey
                         };
            if (m_strName != "")
            {
                values = values.Where(tb => tb.Name.Contains(m_strName));
                //if (m_iDepartment > 0)
                //    values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartment);
            }
            else
            {
                if (m_iDepartment > 0)
                    values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartment);
                if (m_iMajor > 0)
                    values = values.Where(tb => tb.Catalogue_MajorKey == m_iMajor);
                if (m_iLevel > 0)
                    values = values.Where(tb => tb.Catalogue_LevelKey == m_iLevel);
                if (m_iType > 0)
                    values = values.Where(tb => tb.Catalogue_TrainingType == m_iType);
                if (m_iCourse > 0)
                    values = values.Where(tb => tb.Catalogue_CourseKey == m_iCourse);
            }
            return values;
        }
        #endregion

        #region [Chi]
        #region
        /// <summary>
        /// Thống Kê Lớp Theo Khoa
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_Department></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByDepartment(this Table<Training_Class> trainingClass,
                                        Table<Catalogue_Department> catalogue_Department)
        {
            var Query = from Cls in trainingClass
                        join join_Table in catalogue_Department on Cls.Catalogue_DepartmentKey equals join_Table.Catalogue_DepartmentKey
                        group join_Table by new
                        {
                            join_Table.Catalogue_DepartmentKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_DepartmentKey = Group.Key.Catalogue_DepartmentKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        /// <summary>
        /// Thống Kê Lớp Theo Loại Hình Đào Tạo
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_TrainingType></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByTrainingType(this Table<Training_Class> trainingClass,
                                        Table<Catalogue_TrainingType> catalogue_TrainingType)
        {
            var Query = from Cls in trainingClass
                        join join_Table in catalogue_TrainingType on Cls.Catalogue_TrainingType equals join_Table.Catalogue_TrainingTypeKey
                        group join_Table by new
                        {
                            join_Table.Catalogue_TrainingTypeKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_TrainingTypeKey = Group.Key.Catalogue_TrainingTypeKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        /// <summary>
        /// Thống Kê Lớp Theo Bậc Đào Tạo
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_Level></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByLevel(this Table<Training_Class> trainingClass,
                                        Table<Catalogue_Level> catalogue_Level)
        {
            var Query = from Cls in trainingClass
                        join join_Table in catalogue_Level on Cls.Catalogue_LevelKey equals join_Table.Catalogue_LevelKey
                        group join_Table by new
                        {
                            join_Table.Catalogue_LevelKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_LevelKey = Group.Key.Catalogue_LevelKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        /// <summary>
        /// Thống Kê Lớp Theo Khóa
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_Course></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByCourse(this Table<Training_Class> trainingClass,
                                        Table<Catalogue_Course> catalogue_Course)
        {
            var Query = from Cls in trainingClass
                        join join_Table in catalogue_Course on Cls.Catalogue_CourseKey equals join_Table.Catalogue_CourseKey
                        group join_Table by new
                        {
                            join_Table.Catalogue_CourseKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_CourseKey = Group.Key.Catalogue_CourseKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        /// <summary>
        /// Thống Kê Lớp Theo Ngành
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_Major></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByMajor(this Table<Training_Class> trainingClass,
                                        Table<Catalogue_Major> catalogue_Major)
        {
            var Query = from Cls in trainingClass
                        join join_Table in catalogue_Major on Cls.Catalogue_MajorKey equals join_Table.Catalogue_MajorKey
                        group join_Table by new
                        {
                            join_Table.Catalogue_MajorKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_MajorKey = Group.Key.Catalogue_MajorKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        /// <summary>
        /// Thống Kê Lớp Theo Giáo Viên Chủ Nhiệm
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=personnel_Personnel></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_ClassByPersonnel(this Table<Training_Class> trainingClass,
                                        Table<Personnel_Personnel> personnel_Personnel)
        {
            var Query = from Cls in trainingClass
                        join join_Table in personnel_Personnel on Cls.Personnel_PersonnelKey equals join_Table.Personnel_PersonnelKey
                        group join_Table by new
                        {
                            join_Table.Personnel_PersonnelKey,
                            join_Table.Name
                        } into Group
                        select new
                        {
                            Catalogue_personnel_PersonnelKey = Group.Key.Personnel_PersonnelKey,
                            Group.Key.Name,
                            Total = Group.Count()
                        };
            return Query;
        }
        #endregion
        #region
        public static IQueryable
            GetDepartmentInTrainingClass(this Table<Training_Class> nTrainingClass,
                                        Table<Catalogue_Department> nDepartment)
        {
            var department = (from t in nTrainingClass
                              join t0 in nDepartment on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t.Catalogue_DepartmentKey,
                                  t0.Name
                              }).Distinct();
            return department.Distinct();
        }
        /// <summary>
        /// Lấy bậc đào tạo trong lớp
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=nDepartment></param>
        /// <returns></returns>
        public static IQueryable
            GetLevelInTrainingClass(this Table<Training_Class> nTrainingClass,
                                         Table<Catalogue_Level> nLevel)
        {
            var level = (from t in nTrainingClass
                         join t0 in nLevel on new { Catalogue_LevelKey = Convert.ToInt32(t.Catalogue_LevelKey) } equals new { Catalogue_LevelKey = t0.Catalogue_LevelKey }
                         select new
                         {
                             Catalogue_LevelKey = (System.Int32?)t.Catalogue_LevelKey,
                             t0.Name
                         }).Distinct();
            return level.Distinct();
        }
        /// <summary>
        /// Lấy ngành trong lớp
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=nDepartment></param>
        /// <returns></returns>
        public static IQueryable
            GetMajorInTrainingClass(this Table<Training_Class> nTrainingClass,
                                    Table<Catalogue_Major> nMajor)
        {
            var major = (from t in nTrainingClass
                         join t0 in nMajor on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                         select new
                         {
                             Catalogue_MajorKey = (System.Int32?)t.Catalogue_MajorKey,
                             t0.Name,
                             t0.Catalogue_DepartmentKey
                         }).Distinct();
            return major.Distinct();
        }
        /// <summary>
        /// Lấy loại hình trong lớp
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=nDepartment></param>
        /// <returns></returns>
        public static IQueryable
            GetTrainingTypeInTrainingClass(this Table<Training_Class> nTrainingClass,
                                                Table<Catalogue_TrainingType> nTrainingType)
        {
            var trainingtype = (from t in nTrainingClass
                                join t0 in nTrainingType on new { Catalogue_TrainingType = Convert.ToInt32(t.Catalogue_TrainingType) } equals new { Catalogue_TrainingType = t0.Catalogue_TrainingTypeKey }
                                select new
                                {
                                    t.Catalogue_TrainingType,
                                    t0.Name
                                }).Distinct();
            return trainingtype.Distinct();
        }
        /// <summary>
        /// Lấy khóa học trong lớp
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=nDepartment></param>
        /// <returns></returns>
        public static IQueryable
            GetCourseInTrainingClass(this Table<Training_Class> nTrainingClass,
                                     Table<Catalogue_Course> nCourse)
        {
            var course = (from t in nTrainingClass
                          join t0 in nCourse on new { Catalogue_CourseKey = Convert.ToInt32(t.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t0.Catalogue_CourseKey }
                          select new
                          {
                              Catalogue_CourseKey = (System.Int32?)t.Catalogue_CourseKey,
                              t0.Name
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy bậc đào tạo với khoa
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <returns></returns>
        public static IQueryable
            GetLevelByDepartment(this Table<Training_Class> nTrainingClass,
                                 Table<Catalogue_Level> nLevel,
                                 int m_iDepartmentKey)
        {
            var level = (from t in nTrainingClass
                         join t0 in nLevel on new { Catalogue_LevelKey = Convert.ToInt32(t.Catalogue_LevelKey) } equals new { Catalogue_LevelKey = t0.Catalogue_LevelKey }
                         where
                           t.Catalogue_DepartmentKey == m_iDepartmentKey
                         select new
                         {
                             Catalogue_LevelKey = (System.Int32?)t.Catalogue_LevelKey,
                             t0.Name
                         }).Distinct();
            return level.Distinct();
        }
        /// <summary>
        /// Lấy ngành với khoa
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <returns></returns>
        public static IQueryable
            GetMajorByDepartment(this Table<Training_Class> nTrainingClass,
                                 Table<Catalogue_Major> nMajor,
                                 int m_iDepartmentKey)
        {
            var major = (from t in nTrainingClass
                         join t0 in nMajor on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                         where
                           t.Catalogue_DepartmentKey == m_iDepartmentKey
                         select new
                         {
                             Catalogue_MajorKey = (System.Int32?)t.Catalogue_MajorKey,
                             t0.Name,
                         }).Distinct();
            return major.Distinct();
        }
        /// <summary>
        /// Lấy loại hình đào tạo với khoa
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <returns></returns>
        public static IQueryable
            GetTrainingTypeByDepartment(this Table<Training_Class> nTrainingClass,
                                        Table<Catalogue_TrainingType> nTrainingType,
                                        int m_iDepartmentKey)
        {
            var trainingtype = (from t in nTrainingClass
                                join t0 in nTrainingType on new { Catalogue_TrainingType = Convert.ToInt32(t.Catalogue_TrainingType) } equals new { Catalogue_TrainingType = t0.Catalogue_TrainingTypeKey }
                                where
                                  t.Catalogue_DepartmentKey == m_iDepartmentKey
                                select new
                                {
                                    Catalogue_TrainingType = (System.Int32?)t.Catalogue_TrainingType,
                                    t0.Name
                                }).Distinct();
            return trainingtype.Distinct();
        }
        /// <summary>
        /// Lấy khóa học với khoa
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <returns></returns>
        public static IQueryable
            GetCourseByDepartment(this Table<Training_Class> nTrainingClass,
                                    Table<Catalogue_Course> nCourse,
                                    int m_iDepartmentKey)
        {
            var course = (from t in nTrainingClass
                          join t0 in nCourse on new { Catalogue_CourseKey = Convert.ToInt32(t.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t0.Catalogue_CourseKey }
                          where
                            t.Catalogue_DepartmentKey == m_iDepartmentKey
                          select new
                          {
                              Catalogue_CourseKey = (System.Int32?)t.Catalogue_CourseKey,
                              t0.Name,
                          }).Distinct();
            return course.Distinct();
        }
        /// <summary>
        /// Lấy mã ngành với khoa, bậc đào tạo
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=m_iDepartmentKey></param>
        /// <param name=m_iLevel></param>
        /// <returns></returns>
        public static IQueryable
            GetMajorKeyByDepartmentLevel(this Table<Training_Class> nTrainingClass,
                                            int m_iDepartmentKey, int m_iLevel)
        {
            var major = (from t in nTrainingClass
                         where
                           t.Catalogue_DepartmentKey == m_iDepartmentKey &&
                           t.Catalogue_LevelKey == m_iLevel
                         select new
                         {
                             t.Catalogue_MajorKey
                         }).Distinct();
            return major.Distinct();
        }
        /// <summary>
        /// Lấy mã khoa với ngành
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=m_iLevel></param>
        /// <returns></returns>
        public static IQueryable
            GetDepartmentKeyByMajor(this Table<Training_Class> nTrainingClass,
                                    int m_iMajor)
        {
            var department = (from t in nTrainingClass
                              where
                                t.Catalogue_MajorKey == m_iMajor
                              select new
                              {
                                  t.Catalogue_DepartmentKey
                              }).Distinct();
            return department.Distinct();
        }
        /// <summary>
        /// lấy mã khoa với ngành
        /// </summary>
        /// <param name=nTrainingClass></param>
        /// <param name=m_iDepartmentKey></param>
        /// <param name=m_iMajor></param>
        /// <returns></returns>
        public static IQueryable
            GetLevelKeyByDepartmentMajor(this Table<Training_Class> nTrainingClass,
                                         int m_iDepartmentKey, int m_iMajor)
        {
            var level = (from t in nTrainingClass
                         where
                           t.Catalogue_DepartmentKey == m_iDepartmentKey &&
                           t.Catalogue_MajorKey == m_iMajor
                         select new
                         {
                             t.Catalogue_LevelKey
                         }).Distinct();
            return level.Distinct();
        }
        /// <summary>
        /// Lấy giá trị trong LookUpEdit chyển sang kiểu int
        /// </summary>
        /// <param name=lue></param>
        /// <returns></returns>
        public static int ConvertValueLookUpEditToInt(object lue)
        {
            if (lue != null)
            {
                return int.Parse(lue.ToString());
            }
            else
            {
                return 0;
            }
        }
        #endregion
        /// <summary>
        /// lấy danh sách lớp theo điều kiện lọc
        /// </summary>
        /// <param name=trainingClass></param>
        /// <param name=catalogue_Course></param>
        /// <param name=catalogue_Department></param>
        /// <param name=catalogue_Level></param>
        /// <param name=catalogue_Major></param>
        /// <param name=catalogue_TrainingType></param>
        /// <param name=catalogue_CourseKey></param>
        /// <param name=catalogue_DepartmentKey></param>
        /// <param name=catalogue_LevelKey></param>
        /// <param name=catalogue_MajorKey></param>
        /// <param name=catalogue_TrainingTypeKey></param>
        /// <returns></returns>
        public static IQueryable
           GetReport_Class(this Table<Training_Class> trainingClass,
                            Table<Catalogue_Course> catalogue_Course,
                            Table<Catalogue_Department> catalogue_Department,
                            Table<Catalogue_Level> catalogue_Level,
                            Table<Catalogue_Major> catalogue_Major,
                            Table<Catalogue_TrainingType> catalogue_TrainingType,
                            int catalogue_CourseKey = -1,
                            int catalogue_DepartmentKey = -1,
                            int catalogue_LevelKey = -1,
                            int catalogue_MajorKey = -1,
                            int catalogue_TrainingTypeKey = -1,
                            int m_iDpt = 0,
                            int m_iLel = 0,
                            int m_iMaj = 0,
                            int m_iTty = 0,
                            int m_iCou = 0)
        {
            #region
            var Query = from Class in trainingClass
                        join Course in catalogue_Course on Class.Catalogue_CourseKey equals Course.Catalogue_CourseKey
                        join Department in catalogue_Department on Class.Catalogue_DepartmentKey equals Department.Catalogue_DepartmentKey
                        join Level in catalogue_Level on Class.Catalogue_LevelKey equals Level.Catalogue_LevelKey
                        join Major in catalogue_Major on Class.Catalogue_MajorKey equals Major.Catalogue_MajorKey
                        join TrainingType in catalogue_TrainingType on Class.Catalogue_TrainingType equals TrainingType.Catalogue_TrainingTypeKey
                        select new
                        {
                            Class.Catalogue_DepartmentKey,
                            Catalogue_DepartmentName = Department.Name,
                            Class.Catalogue_TrainingType,
                            Catalogue_TrainingTypeName = TrainingType.Name,
                            Class.Catalogue_LevelKey,
                            Catalogue_LevelName = Level.Name,
                            Class.Catalogue_CourseKey,
                            Catalogue_CourseName = Course.Name,
                            Class.Catalogue_MajorKey,
                            Catalogue_MajorName = Major.Name,
                            Class.ID,
                            Class.Name
                        };
            if (catalogue_CourseKey != -1)
            {
                Query = Query.Where(q => q.Catalogue_CourseKey == catalogue_CourseKey);
            }
            if (catalogue_DepartmentKey != -1)
            {
                Query = Query.Where(q => q.Catalogue_DepartmentKey == catalogue_DepartmentKey);
            }
            if (catalogue_LevelKey != -1)
            {
                Query = Query.Where(q => q.Catalogue_LevelKey == catalogue_LevelKey);
            }
            if (catalogue_MajorKey != -1)
            {
                Query = Query.Where(q => q.Catalogue_MajorKey == catalogue_MajorKey);
            }
            if (catalogue_TrainingTypeKey != -1)
            {
                Query = Query.Where(q => q.Catalogue_TrainingType == catalogue_TrainingTypeKey);
            }
            #endregion
            #region
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_MajorName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt == 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_MajorName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel == 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj == 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty == 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou == 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            if (m_iDpt != 0 && m_iLel != 0 && m_iMaj != 0 && m_iTty != 0 && m_iCou != 0)
            {
                #region
                var isQuery = from t in Query
                              group t by new
                              {
                                  t.Catalogue_DepartmentKey,
                                  t.Catalogue_DepartmentName,
                                  t.Catalogue_LevelKey,
                                  t.Catalogue_LevelName,
                                  t.Catalogue_MajorKey,
                                  t.Catalogue_MajorName,
                                  t.Catalogue_TrainingType,
                                  t.Catalogue_TrainingTypeName,
                                  t.Catalogue_CourseKey,
                                  t.Catalogue_CourseName
                              } into g
                              select new
                              {
                                  g.Key.Catalogue_DepartmentName,
                                  g.Key.Catalogue_LevelName,
                                  g.Key.Catalogue_MajorName,
                                  g.Key.Catalogue_TrainingTypeName,
                                  g.Key.Catalogue_CourseName,
                                  Total = g.Count()
                              };
                return isQuery;
                #endregion
            }
            #region
            var Query_ = from t in Query
                         group t by t into g
                         select new
                         {
                             g.Key.Catalogue_DepartmentName,
                             g.Key.Catalogue_LevelName,
                             g.Key.Catalogue_MajorName,
                             g.Key.Catalogue_TrainingTypeName,
                             g.Key.Catalogue_CourseName,
                             Total = g.Count()
                         };
            return Query_;
            #endregion
            #endregion
        }
        #endregion

        #region [Hữu]
        /// <summary>
        /// Lấy danh sách Lớp theo khóa ngoại
        /// </summary>
        /// <param name="nClass"></param>
        /// <param name="_iDeptKey"></param>
        /// <param name="_iLevelKey"></param>
        /// <param name="_iTrainingTypeKey"></param>
        /// <param name="_iCourseKey"></param>
        /// <param name="_iMajorKey"></param>
        /// <returns></returns>
        public static IQueryable GetClassByRef(this Table<Training_Class> _nClass,
                                                    int _iDeptKey = -1,
                                                    int _iLevelKey = -1,
                                                    int _iTrainingTypeKey = -1,
                                                    int _iCourseKey = -1,
                                                    int _iMajorKey = -1)
        {
            var source = from cls in _nClass select cls;
            if (_iDeptKey != -1) source = source.Where(p => p.Catalogue_DepartmentKey == _iDeptKey);
            if (_iLevelKey != -1) source = source.Where(p => p.Catalogue_LevelKey == _iLevelKey);
            if (_iTrainingTypeKey != -1) source = source.Where(p => p.Catalogue_TrainingType == _iTrainingTypeKey);
            if (_iCourseKey != -1) source = source.Where(p => p.Catalogue_CourseKey == _iCourseKey);
            if (_iMajorKey != -1) source = source.Where(p => p.Catalogue_MajorKey == _iMajorKey);

            return source;
        }
        #endregion

        #region [Hòa]
        /// <summary>
        /// Lấy danh sách lớp học phần có thể nhập hoặc kiểm tra điểm
        /// </summary>
        /// <param name="nTraining_Subject_Class"></param>
        /// <param name="nTraining_SubjectClass_ScoreType"></param>
        /// <param name="nTraining_Score_TaskInput"></param>
        /// <param name="_dteNow">Thời gian hiện tại</param>
        /// <param name="_bInput">True nếu là nhập điểm, false là kiểm tra điểm</param>
        /// <param name="_bInTask">Đã phân công rồi hay chưa (True là đã phân công)</param>
        /// <returns></returns>
        public static IQueryable GetListCanInputOrCheckScore(
            this Table<Training_Subject_Class> nTraining_Subject_Class,
            Table<Training_SubjectClass_ScoreType> nTraining_SubjectClass_ScoreType,
            Table<Training_Score_TaskInput> nTraining_Score_TaskInput,
            Table<Personnel_Personnel> nPersonnel_Personnel,
            DateTime _dteNow,
            bool _bInput = true,
            bool _bInTask = false)
        {
            var iInTask = (from task in nTraining_Score_TaskInput
                           where task.Input == _bInput
                                 && task.Lock == false
                           //&& task.Date < _dteNow && _dteNow < task.Date.Value.AddDays(task.Period.Value)
                           select task.Training_Subject_ClassKey).Distinct();
            if (_bInput)
            {
                var iQuery = from t in nTraining_Subject_Class
                             join t0 in nTraining_SubjectClass_ScoreType on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                             where
                               t0.Lock == false ||
                               t0.Lock == null
                             group t by new
                             {
                                 t.Training_Subject_ClassKey,
                                 t.ID,
                                 t.Name
                             } into g
                             select new
                             {
                                 Training_Subject_ClassKey = (System.Int32?)g.Key.Training_Subject_ClassKey,
                                 g.Key.ID,
                                 g.Key.Name
                             };
                if (_bInTask)
                {
                    iQuery = from query in iQuery
                             where iInTask.Contains(query.Training_Subject_ClassKey)
                             select query;
                    var task = from scoretask in nTraining_Score_TaskInput
                               from query in iQuery
                               where scoretask.Input == _bInput
                               where scoretask.Lock == false
                               where scoretask.Training_Subject_ClassKey == query.Training_Subject_ClassKey
                               select new
                               {
                                   query.Training_Subject_ClassKey,
                                   query.ID,
                                   query.Name,
                                   scoretask.Personnel_PersonnelKeyLeader,
                                   scoretask.Personnel_PersonnelKey,
                                   scoretask.Date,
                                   scoretask.Period,
                               };

                    var task1 = from tsk in task
                                from personel in nPersonnel_Personnel
                                where tsk.Personnel_PersonnelKey == personel.Personnel_PersonnelKey
                                select new
                                {
                                    tsk.Training_Subject_ClassKey,
                                    tsk.ID,
                                    tsk.Name,
                                    tsk.Date,
                                    tsk.Period,
                                    tsk.Personnel_PersonnelKey,
                                    fullName = personel.LastName + " " + personel.Name,
                                    tsk.Personnel_PersonnelKeyLeader,
                                };
                    var task2 = from tsk in task1
                                from personel in nPersonnel_Personnel
                                where tsk.Personnel_PersonnelKeyLeader == personel.Personnel_PersonnelKey
                                select new
                                {
                                    tsk.Training_Subject_ClassKey,
                                    tsk.ID,
                                    tsk.Name,
                                    tsk.Date,
                                    tsk.Period,
                                    tsk.Personnel_PersonnelKey,
                                    FullName = tsk.fullName,
                                    tsk.Personnel_PersonnelKeyLeader,
                                    FullNameLeader = personel.LastName + " " + personel.Name
                                };
                    return task2;
                }
                else
                {
                    iQuery = from query in iQuery
                             where !iInTask.Contains(query.Training_Subject_ClassKey)
                             select query;
                    return iQuery;
                }
            }
            else
            {
                var iQuery = from t in nTraining_Subject_Class
                             join t0 in nTraining_SubjectClass_ScoreType on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                             where
                               t0.Check == false ||
                               t0.Check == null
                             group t by new
                             {
                                 t.Training_Subject_ClassKey,
                                 t.ID,
                                 t.Name
                             } into g
                             select new
                             {
                                 Training_Subject_ClassKey = (System.Int32?)g.Key.Training_Subject_ClassKey,
                                 g.Key.ID,
                                 g.Key.Name
                             };
                if (_bInTask)
                {
                    iQuery = from query in iQuery
                             where iInTask.Contains(query.Training_Subject_ClassKey)
                             select query;
                    var task = from scoretask in nTraining_Score_TaskInput
                               from query in iQuery
                               where scoretask.Input == _bInput
                               where scoretask.Lock == false
                               where scoretask.Training_Subject_ClassKey == query.Training_Subject_ClassKey
                               select new
                               {
                                   query.Training_Subject_ClassKey,
                                   query.ID,
                                   query.Name,
                                   scoretask.Personnel_PersonnelKeyLeader,
                                   scoretask.Personnel_PersonnelKey,
                                   scoretask.Date,
                                   scoretask.Period,
                               };

                    var task1 = from tsk in task
                                from personel in nPersonnel_Personnel
                                where tsk.Personnel_PersonnelKey == personel.Personnel_PersonnelKey
                                select new
                                {
                                    tsk.Training_Subject_ClassKey,
                                    tsk.ID,
                                    tsk.Name,
                                    tsk.Date,
                                    tsk.Period,
                                    tsk.Personnel_PersonnelKey,
                                    fullName = personel.LastName + " " + personel.Name,
                                    tsk.Personnel_PersonnelKeyLeader,
                                };
                    var task2 = from tsk in task1
                                from personel in nPersonnel_Personnel
                                where tsk.Personnel_PersonnelKeyLeader == personel.Personnel_PersonnelKey
                                select new
                                {
                                    tsk.Training_Subject_ClassKey,
                                    tsk.ID,
                                    tsk.Name,
                                    tsk.Date,
                                    tsk.Period,
                                    tsk.Personnel_PersonnelKey,
                                    FullName = tsk.fullName,
                                    tsk.Personnel_PersonnelKeyLeader,
                                    FullNameLeader = personel.LastName + " " + personel.Name
                                };
                    return task2;
                }
                else
                {
                    iQuery = from query in iQuery
                             where !iInTask.Contains(query.Training_Subject_ClassKey)
                             select query;
                    return iQuery;
                }
            }
        }

        /// <summary>
        /// Lấy thông tin danh sách lớp
        /// </summary>
        /// <param name="nTraining_Subject_Class"></param>
        /// <param name="_ilClassKey">Danh sách mã lớp</param>
        /// <returns></returns>
        public static IQueryable GetListSubjectClass(
            this Table<Training_Subject_Class> nTraining_Subject_Class,
            List<int> _ilClassKey)
        {
            var iQuery = from subjectClass in nTraining_Subject_Class
                         where _ilClassKey.Contains(subjectClass.Training_Subject_ClassKey)
                         select subjectClass;
            return iQuery;
        }

        public static IQueryable GetSubjectClassBySubjectOpenRequest(
            this Table<Training_Subject_Class> nTraining_Subject_Class,
            int _iTraining_SubjectOpenRequestKey)
        {
            return nTraining_Subject_Class.Where(p => p.Training_SubjectOpenRequestKey == _iTraining_SubjectOpenRequestKey);
        }

        public static IQueryable GetListStudentByClassKey(
            this Table<Training_Class> nTrainingClass,
            Table<Training_Student_Class> nTraining_Student_Class,
            Table<Training_Student> nTraining_Student,
            int _iClassKey)
        {
            var studentClass = nTraining_Student_Class.Where(
                p => p.Training_ClassKey == _iClassKey && p.Mainly == true);
            var iQuery = from studentclass in studentClass
                         from student in nTraining_Student
                         where studentclass.Training_StudentKey ==
                            student.Training_StudentKey
                         select new
                         {
                             student.Training_StudentKey,
                             studentclass.ID,
                             student.LastName,
                             student.SureName,
                             Birthday = student.Birthday == null
                                ? student.ExceptionBirthday : student.Birthday.ToString(),
                             studentclass.Training_TrainingProgramKey
                         };
            return iQuery;
        }
        #endregion

        #region [Chi -- Hòa]
        /// Lấy danh sách lớp theo ctđt
        /// <param name="n_Training_Class"></param>
        /// <param name="_iProgramKey"></param>
        /// <returns></returns>
        public static IQueryable GetClassByProgramKey(
            this Table<Training_Class> n_Training_Class,
            Table<Training_Subject_Class> n_Training_Subject_Class,
            Table<Training_TrainingProgram_Detail> n_TrainingProgram_Detail,
            Table<Training_Student_Class> n_Training_Student_Class,
            List<int?> _ilSubjectKey,
            int TrainingProgramKey = -1,
            int CourseKey = -1,
            int DepartmentKey = -1)
        {
            var class_inSubjectClass = from c in n_Training_Class
                                       join sjc in n_Training_Subject_Class
                                           on c.Training_ClassKey
                                           equals sjc.Training_ClassKey
                                       where _ilSubjectKey.Contains(sjc.Training_SubjectKey)
                                       select new
                                       {
                                           c.Catalogue_CourseKey,
                                           c.Catalogue_DepartmentKey,
                                           c.Catalogue_LevelKey,
                                           c.Catalogue_MajorKey,
                                           c.Catalogue_TrainingType,
                                           c.ID,
                                           c.Name,
                                           c.Training_ClassKey,
                                           c.Training_TrainingProgramKey,
                                           c.Yearly,
                                           Status = true
                                       };
            var class_notinSubjectClass = from c in n_Training_Class
                                          join pr_dt in n_TrainingProgram_Detail
                                                on c.Training_TrainingProgramKey
                                                equals pr_dt.Training_TrainingProgramKey
                                          where _ilSubjectKey.Contains(pr_dt.Training_SubjectKey) &&
                                                !(from cc in n_Training_Class
                                                  join sjc in n_Training_Subject_Class
                                                           on cc.Training_ClassKey
                                                           equals sjc.Training_ClassKey
                                                  where _ilSubjectKey.Contains(sjc.Training_SubjectKey)
                                                  select cc.Training_ClassKey).Contains(c.Training_ClassKey)
                                          select new
                                          {
                                              c.Catalogue_CourseKey,
                                              c.Catalogue_DepartmentKey,
                                              c.Catalogue_LevelKey,
                                              c.Catalogue_MajorKey,
                                              c.Catalogue_TrainingType,
                                              c.ID,
                                              c.Name,
                                              c.Training_ClassKey,
                                              c.Training_TrainingProgramKey,
                                              c.Yearly,
                                              Status = false
                                          };
            var ct = class_notinSubjectClass.Union(class_inSubjectClass);
            ct = ct.Distinct();
            if (TrainingProgramKey > -1)
            {
                ct = ct.Where(p => p.Training_TrainingProgramKey == TrainingProgramKey);
            }
            if (CourseKey > -1)
            {
                ct = ct.Where(p => p.Catalogue_CourseKey == CourseKey);
            }
            if (DepartmentKey > -1)
            {
                ct = ct.Where(p => p.Catalogue_DepartmentKey == DepartmentKey);
            }
            var ctr = from hhh in ct
                      join hhhh in n_Training_Student_Class
                                on hhh.Training_ClassKey
                                equals hhhh.Training_ClassKey
                      group hhh by new
                      {
                          hhh.Training_ClassKey,
                          hhh.Training_TrainingProgramKey,
                          hhh.Status,
                          hhh.Yearly,
                          hhh.Name,
                          hhh.ID,
                          hhh.Catalogue_TrainingType,
                          hhh.Catalogue_MajorKey,
                          hhh.Catalogue_LevelKey,
                          hhh.Catalogue_DepartmentKey,
                          hhh.Catalogue_CourseKey
                      } into g
                      select new
                      {
                          g.Key.Catalogue_CourseKey,
                          g.Key.Catalogue_DepartmentKey,
                          g.Key.Catalogue_LevelKey,
                          g.Key.Catalogue_MajorKey,
                          g.Key.Catalogue_TrainingType,
                          g.Key.ID,
                          g.Key.Name,
                          g.Key.Status,
                          g.Key.Training_ClassKey,
                          g.Key.Training_TrainingProgramKey,
                          g.Key.Yearly,
                          Count = g.Count()
                      };
            return ctr;
        }
        #endregion

        #region [Rpt - Thảo]
        /// <summary>
        /// Lấy lớp theo khóa, ngành
        /// </summary>
        /// <param name="trainingClass"></param>
        /// <param name="cataCou"></param>
        /// <param name="cataMaj"></param>
        /// <param name="_iCou"></param>
        /// <param name="_iMaj"></param>
        /// <returns></returns>
        public static IQueryable GetTraining_ClassData(this Table<Training_Class> trainingClass, int _iCouserKey, int _iDepartmentKey, int _iMajorKey)
        {
            var view2 = from _class in trainingClass
                        where _class.Catalogue_CourseKey == _iCouserKey
                        where _class.Catalogue_MajorKey == _iMajorKey
                        where _class.Catalogue_DepartmentKey == _iDepartmentKey
                        select _class;
            return view2;
        }
        #endregion
    }
}
